const connection = require("../app/database");
class momentService {
  // 创建动态
  async create(content, userId) {
    // 存储数据的sql
    const statement = "INSERT INTO `moment` (content, user_id) VALUES (?, ?);";
    const [result] = await connection.execute(statement, [content, userId]);
    // 返回存储结果
    return result;
  }

  // 查询所有动态
  async queryList(offset = 0, size = 5) {
    const statement = `SELECT 
      m.id id,m.content content,m.createAt createTime,m.updateAt updateTime,
      JSON_OBJECT('id',u.id,'name',u.name,'avatarUrl',u.avatar_url,'createTime',u.createAt,'updateTime',u.updateAt) user,
      (SELECT COUNT(*) FROM comment WHERE comment.moment_id = m.id) commentCount,
      (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelsCount
          FROM moment m 
          LEFT JOIN user u 
          ON m.user_id = u.id 
          LIMiT ? OFFSET ?;
      `;
    const [result] = await connection.execute(statement, [
      String(size),
      String(offset),
    ]);
    return result;
  }

  // 查询某一个动态的详情
  async queryById(momentId) {
    const statement = `SELECT 
      m.id id,m.content content,m.createAt createTime,m.updateAt updateTime,
      JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'updateTime',u.updateAt) user,
        (
          SELECT 
            JSON_ARRAYAGG(
              JSON_OBJECT(
                'id', c.id, 'content', c.content, 'commentId', c.comment_id,
                'user', JSON_OBJECT('id', cu.id, 'name', cu.name)
            )) 
          FROM comment c 
          LEFT JOIN user cu ON c.user_id = cu.id
          WHERE c.moment_id = m.id
        ) comments,
        (
          JSON_ARRAYAGG(JSON_OBJECT('id', l.id, 'name', l.name))
        ) labels
      FROM moment m 
      LEFT JOIN user u ON m.user_id = u.id
      LEFT JOIN moment_label ml ON ml.moment_id = m.id
      LEFT JOIN label l ON l.id = ml.label_id
      WHERE m.id = 31
      GROUP BY m.id`;

    const [result] = await connection.execute(statement, [momentId]);
    return result;
  }

  async updataMonent(content, momentId) {
    const statement = "UPDATE moment SET content = ? WHERE id = ?;";

    const [result] = await connection.execute(statement, [content, momentId]);
    return result;
  }

  async deleteMoment(momentId) {
    const statement = "DELETE FROM moment WHERE id = ?;";

    const [result] = await connection.execute(statement, [momentId]);
    console.log(result);
    return result;
  }
}

module.exports = new momentService();
